package org.unt.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class Db extends DbBean {

	public Db() {
	}

	public Db(Properties properties, String str) throws ClassNotFoundException, SQLException {

		try {
			init(properties, str);
		} catch (RuntimeException e) {
			logger.error(e.getMessage());
			System.exit(-1);
		}
	}

	private void init(Properties properties, String dbStr) throws ClassNotFoundException, SQLException {

		if (dbStr.indexOf("db2") > -1) {
			this.dbType = "db2";
		} else if (dbStr.indexOf("ora") > -1) {
			this.dbType = "ora";
		} else {
			throw new RuntimeException("Can't decide if Oracle u DB2 : " + dbStr);
		}

		url = properties.getProperty("db.url." + dbStr);

		if (url == null || url.equals(""))
			throw new RuntimeException("Can't find db configuration in 'config.properties' for : " + dbStr);

		driver = properties.getProperty("db.driver." + dbStr);
		user = properties.getProperty("db.user." + dbStr);
		pass = properties.getProperty("db.pass." + dbStr);
		schema = properties.getProperty("db.schema." + dbStr);

		try {

			Class.forName(driver);

		} catch (ClassNotFoundException e) {
			logger.error("Cannot load driver");
			System.exit(-1);
		}

		try {

			conn = DriverManager.getConnection(url, user, pass);

		} catch (SQLException e) {
			logger.error("Cannot get connection for " + dbStr);
			System.exit(-1);
		}

		try {

			statement = conn.createStatement();

		} catch (SQLException e) {
			logger.error("Cannot create statement on connection for " + dbStr);
			System.exit(-1);
		}
	}

	public int deleteTable(String table) throws SQLException {

		try {

			return conn.createStatement().executeUpdate("delete from " + schema + "." + table);

		} catch (SQLException e) {
			logger.error("Cannot delete table " + table);
			throw e;
		}
	}

	public int executeUpdate(String update) throws SQLException {
		return conn.createStatement().executeUpdate(update);
	}

	/**
	 * Free select. Needs the correct schema.
	 */
	public ResultSet select(String select) throws SQLException {

		logger.debug(select);
		return statement.executeQuery(select);
	}

	public List<String> selectOneColumnString(String select) throws SQLException {
		List<String> list = new ArrayList<String>();
		ResultSet rs = select(select);
		while (rs.next()) {
			list.add(rs.getString(1));
		}
		return list;
	}

	public Object selectOne(String select) throws SQLException {

		ResultSet rs = select(select);
		rs.next();
		return rs.getObject(1);
	}

	/**
	 * From 'table' returns 'schema.table'
	 * 
	 * @param table
	 * @return
	 */
	public String addSchema(String table) {
		return schema + "." + table;
	}

	/**
	 * Select count(*) from table. Does not need the schema.
	 */
	public int count(String table) throws SQLException {

		String sql = "select count(*) from " + schema + "." + table;
		logger.debug("SQL > " + sql);

		ResultSet rs = select(sql);
		rs.next();
		int count = rs.getInt(1);
		logger.debug("Got " + count);

		rs.close();

		return count;
	}

	public Map getCounts(List tables) throws SQLException {

		Map map = new HashMap();

		for (Iterator iterator = tables.iterator(); iterator.hasNext();) {

			String table = (String) iterator.next();
			int i = count(table);

			logger.debug(table + " count " + i);

			map.put(table, new Integer(i));
		}

		return map;
	}

	public void deleteTables(List tables) throws SQLException {

		System.out.println();
		for (Iterator iterator = tables.iterator(); iterator.hasNext();) {

			String table = (String) iterator.next();
			deleteTable(table);

			logger.info(table + " deleted");
		}
	}

	public void commit() throws SQLException {
		conn.commit();
	}

	public PreparedStatement buildInsertPrepared(String table, List<Column> columns) throws SQLException {

		StringBuffer buf1 = new StringBuffer();
		StringBuffer buf2 = new StringBuffer();

		for (Column column : columns) {
			buf1.append(column.getName() + ",");
			buf2.append("?,");
		}

		String insert = "insert into " + getSchema() + "." + table + //
				" (" + buf1.substring(0, buf1.length() - 1) + ") values (" + //
				buf2.substring(0, buf2.length() - 1) + ")";
		logger.debug("SQL > " + insert);

		return getConn().prepareStatement(insert);
	}

	public int update(String update) throws SQLException {

		logger.debug(update);
		System.out.println(update);
		statement.executeUpdate(update);
		return -1;
	}

	public Connection getConnection() {
		return conn;
	}

	/**
	 * orderBy Can either be 'NAME' or 'ORDEN'
	 */
	public List getColumnsFromTable(String table, String orderBy) throws SQLException {

		ResultSet rs = null;

		if (dbType.indexOf("db2") > -1) {

			rs = select( // --------- > DB2
			//
			//
			"select NAME, COLTYPE TYPE, LENGTH, SCALE, NULLS NULLABLE, COLNO ORDEN " + //
					"from SYSIBM.SYSCOLUMNS " + //
					"where TBNAME = '" + table + "' and TBCREATOR = '" + schema + "' order by " + orderBy
			//
			);

		} else if (dbType.indexOf("ora") > -1) {

			rs = select( // --------- > ORACLE
			//
			//
			"select COLUMN_NAME NAME, DATA_TYPE TYPE, DATA_LENGTH LENGTH, DATA_SCALE SCALE, NULLABLE, COLUMN_ID ORDEN " + //
					"from ALL_TAB_COLUMNS " + //
					"where TABLE_NAME = '" + table + "' and OWNER = '" + schema + "' order by " + orderBy
			//
			);
		}

		List columns = new ArrayList();
		while (rs.next()) {

			String name = rs.getString("NAME").trim();
			String type = DbLogic.doStandardType(rs.getString("TYPE").trim(), rs.getInt("SCALE"));
			int length = rs.getInt("LENGTH");
			int scale = rs.getInt("SCALE");
			boolean nullable = "Y".equals(rs.getString("NULLABLE").trim());

			if ("TIMESTAMP".equals(type)) {
				scale = 0;
			}

			Column column = new Column(name, type, length, scale, nullable);

			if (name.equals("VALUE")) {

				logger.debug(rs.getString("NAME").trim() + "\t" + rs.getString("TYPE").trim() + "\t" + //
						rs.getInt("LENGTH") + "\t" + rs.getInt("SCALE") + "\t" + rs.getString("NULLABLE").trim());
				logger.debug(column);
			}

			columns.add(column);
		}
		logger.debug("");

		return columns;
	}
}
